﻿/* 
 * SERVICE CORE - MARC-HI SYNDICATION SCHEMA
 * VERSION: 3.0
 * AUTHOR: JUSTIN FYFE
 * DATE: MARCH 15, 2012
 * FILES:
 *	SUB.SQL		- SQL CODE TO CREATE SYNDICATION DATABASE
 * LICENSE:
 * 	Licensed under the Apache License, Version 2.0 (the "License");
 * 	you may not use this file except in compliance with the License.
 * 	You may obtain a copy of the License at
 *
 *     		http://www.apache.org/licenses/LICENSE-2.0
 *
 * 	Unless required by applicable law or agreed to in writing, software
 * 	distributed under the License is distributed on an "AS IS" BASIS,
 * 	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * 	See the License for the specific language governing permissions and
 * 	limitations under the License.
 *
 * REMARKS:
 *   CONTAINS A LIST OF SUBSCRIPTIONS
 */

 --ALTER DATABASE shr SET bytea_output='ESCAPE';
--CREATE LANGUAGE plpgsql;

DROP SEQUENCE SUB_REC_SEQ CASCADE;

DROP TABLE SUB_TBL CASCADE;
DROP TABLE SUB_REC_TBL CASCADE;

-- @TABLE
-- SUBSCRIPTIONS TABLE
-- CONTAINS A LIST OF SUBSCRIPTIONS AND THE FILTER EXPRESSION FOR THAT SUBSCRIPTION
CREATE TABLE SUB_TBL (
	SUB_ID		VARCHAR(48) NOT NULL, -- GUID OF THE SUBSCRIPTION
	FLTR		BYTEA NOT NULL, -- SUBSCRIPTION DATA
	AUT_ENT_ID	VARCHAR(48) NOT NULL, -- THE LOCAL IDENTIFIER OF THE AUTHOR THAT CREATED THE SUBSCRIPTION
	CRT_UTC		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- TIME THE SUBSCRIPTION IS ACTIVE
	BLD_UTC		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- THE LAST TIME THE SUBSCRIPTION WAS UPDATED
	OBSLT_UTC	TIMESTAMPTZ, -- TIME THE SUBSCRIPTION IS CANCELLED
	CONSTRAINT PK_SUB_TBL PRIMARY KEY (SUB_ID)
);

-- SEQUENCE FOR THE SUBSCRIPTION RECORDS
CREATE SEQUENCE SUB_REC_SEQ START WITH 1 INCREMENT BY 1;

-- @TABLE 
-- SUBSCRIPTION RESULT TABLE
-- CONTAINS A LIST OF SUBSCRIPTIONS FOR THE 
CREATE TABLE SUB_REC_TBL (
	REC_ID		DECIMAL(20,0) NOT NULL DEFAULT nextval('SUB_REC_SEQ'), -- ID OF THE SUBSCRIPTION ITEM
	SUB_ID		VARCHAR(48) NOT NULL, -- GUID OF THE SUBSCRIPTION
	ENT_ID		VARCHAR(48) NOT NULL, -- ID OF THE RECORD THAT MATCHES THE SUBSCRIPTION
	CRT_UTC		TIMESTAMPTZ NOT NULL, -- CREATION TIME OF THE EVENT
	PUB_UTC		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- TIME THE ENTRY WAS AUTHORED 
	DSCLS_UTC	TIMESTAMPTZ, -- TIME THE RECORD WAS DISCLOSED
	MATCHED		BYTEA NOT NULL, -- THE MATCHED PARAMETER(S)
	CONSTRAINT PK_SUB_REC_TBL PRIMARY KEY (REC_ID),
	CONSTRAINT FK_SUB_REC_SUB_TBL FOREIGN KEY (SUB_ID) REFERENCES SUB_TBL(SUB_ID)
);

-- @INDEX
-- LOOKUP RECORDS BY SUBSCRIPTION ID
CREATE INDEX SUB_REC_SUB_ID_IDX ON SUB_REC_TBL(SUB_ID);

-- @FUNCTION 
-- CREATE A SUBSCRIPTION
CREATE OR REPLACE FUNCTION CRT_SUB
(
	SUB_ID_IN	IN VARCHAR(48),
	FLTR_IN		IN BYTEA,
	AUT_ENT_ID_IN	IN VARCHAR(48)
) RETURNS VOID AS
$$
BEGIN
	INSERT INTO SUB_TBL (SUB_ID, FLTR, AUT_ENT_ID) VALUES
		(SUB_ID_IN, FLTR_IN, AUT_ENT_ID_IN);
END
$$
LANGUAGE plpgsql;

-- @FUNCTION 
-- GET SUBSCRIPTIONS
CREATE OR REPLACE FUNCTION GET_ACTV_SUB () RETURNS SETOF SUB_TBL AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM SUB_TBL WHERE OBSLT_UTC IS NULL;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION 
-- GET SUBSCRIPTIONS
CREATE OR REPLACE FUNCTION GET_SUB (
	SUB_ID_IN 	IN VARCHAR(48)	
) RETURNS SETOF SUB_TBL AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM SUB_TBL WHERE OBSLT_UTC IS NULL AND SUB_ID = SUB_ID_IN;
END
$$ LANGUAGE plpgsql;

-- @TABLE
-- GET ALL ACTIVE SUBSCRIPTIONS FOR A USER
CREATE OR REPLACE FUNCTION GET_ACTV_SUB(
	AUT_ENT_ID_IN	IN VARCHAR(48)
) RETURNS SETOF SUB_TBL AS
$$
BEGIN
	RETURN QUERY SELECT * FROM SUB_TBL WHERE AUT_ENT_ID = AUT_ENT_ID_IN AND OBSLT_UTC IS NULL;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- OBSOLETE SUBSCRIPTION
CREATE OR REPLACE FUNCTION OBSLT_SUB
(
	SUB_ID_IN	IN VARCHAR(48)
) RETURNS VOID AS
$$
BEGIN
	UPDATE SUB_TBL SET OBSLT_UTC = CURRENT_TIMESTAMP WHERE SUB_ID = SUB_ID_IN;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- ADD A RECORD TO THE SUBSCRIPTION
CREATE OR REPLACE FUNCTION ADD_SUB_REC
(
	SUB_ID_IN	IN VARCHAR(48),
	ENT_ID_IN	IN VARCHAR(48),
	CRT_UTC_IN	IN TIMESTAMPTZ,
	MATCHED_IN	IN BYTEA
) RETURNS VOID AS
$$
BEGIN
	INSERT INTO SUB_REC_TBL (SUB_ID, ENT_ID, CRT_UTC, MATCHED) VALUES (SUB_ID_IN, ENT_ID_IN, CRT_UTC_IN, MATCHED_IN);
	UPDATE SUB_TBL SET BLD_UTC = CURRENT_TIMESTAMP WHERE SUB_ID = SUB_ID_IN;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- GET RECORDS FROM A SUBSCRIPTION
CREATE OR REPLACE FUNCTION GET_SUB_REC
(
	SUB_ID_IN	IN VARCHAR(48),
	LIMIT_IN	IN DECIMAL(3)
) RETURNS SETOF SUB_REC_TBL AS
$$
BEGIN
	RETURN QUERY SELECT * FROM SUB_REC_TBL WHERE SUB_ID = SUB_ID_IN ORDER BY CRT_UTC DESC LIMIT LIMIT_IN;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- GET RECORDS FROM A SUBSCRIPTION OF ONLY NEW ARTICLES
CREATE OR REPLACE FUNCTION GET_SUB_REC_NEW
(
	SUB_ID_IN	IN VARCHAR(48)
) RETURNS SETOF SUB_REC_TBL AS
$$
BEGIN
	RETURN QUERY SELECT * FROM SUB_REC_TBL WHERE SUB_ID = SUB_ID_IN AND DSCLS_UTC IS NULL ORDER BY CRT_UTC DESC;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- MARKS ALL RECORDS IN THE SUBSCRIPTION AS DISCLOSED
CREATE OR REPLACE FUNCTION MARK_SUB_REC_DSCLS
(
	SUB_ID_IN	IN VARCHAR(48)
) RETURNS VOID AS
$$
BEGIN
	UPDATE SUB_REC_TBL SET DSCLS_UTC = CURRENT_TIMESTAMP 
		WHERE SUB_ID = SUB_ID_IN AND DSCLS_UTC IS NULL;
END
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- GET SUBSCRIPTION ITEM
CREATE OR REPLACE FUNCTION GET_SUB_ITM
(
	SUB_ID_IN	IN VARCHAR(48),
	REC_ID_IN	IN DECIMAL
) RETURNS SETOF SUB_REC_TBL AS
$$
BEGIN
	UPDATE SUB_REC_TBL SET DSCLS_UTC = CURRENT_TIMESTAMP WHERE REC_ID = REC_ID_IN AND SUB_ID = SUB_ID_IN;
	RETURN QUERY SELECT * FROM SUB_REC_TBL WHERE REC_ID = REC_ID_IN AND SUB_ID = SUB_ID_IN;
END;
$$ LANGUAGE plpgsql;